package h_eekwan.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import h_eekwan.interfaces.J20_BookDBService1;
import h_eekwan.module.J20_BookDBVO1;

public class J20_BookDBDAO1 implements J20_BookDBService1 {
	// DAO : data access obbject
	// JDBC 접속정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe"; // 오라클은 1521,mysql은 3306 포트
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	// SQL문 작성
	private static final String selectSQL =
			" select * from bookdb order by id desc ";
//	private static final String CselectSQL =
//			" select id from bookdb order by id desc ";
	
	private static final String insertSQL =
			" insert into bookdb(id,title,publisher,author) values(?, ?, ?, ?) ";
	
	private static final String updateSQL =
			" update bookdb set title = ?, publisher =?, author = ? where id = ?  ";
	
	private static final String deleteSQL =
			" delete from bookdb where id = ? ";

	
	@Override
	public String selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i = 1; 5>=i; ++i)
					result += rs.getString(i)+ "  ";
					result += "\n";
			}
		}catch(Exception ex){
			ex.printStackTrace();			
		}finally{
			if ( rs != null)
				try { rs.close(); } catch(Exception e){}
			if ( ps != null)
				try { ps.close(); } catch(Exception e){}
			if ( conn != null)
				try { conn.close(); } catch(Exception e){}	
		}	
		
		return result;
	}
	
	@Override
	public int addBookDB(J20_BookDBVO1 bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		
		try{
			Class.forName(DRV);
		conn = DriverManager.getConnection(URL, USR, PWD);
		ps = conn.prepareStatement(insertSQL);
			ps.setString(1, bvo.getId());
			ps.setString(2, bvo.getTitle());
			ps.setString(3, bvo.getPublisher());
			ps.setString(4, bvo.getAuthor());
		int cnt = ps.executeUpdate();
		
		if(cnt > 0) result = 1;	// 값받는 순서는 conn > ps > cnt로 받으니까 역순으로 객체를 다룬다.
		} catch(Exception ex) {
			ex.printStackTrace();
		}finally{
			if( ps != null) 
				try { ps.close(); } catch(Exception e){}
			if( conn != null)
				try{ conn.close(); } catch ( Exception e ) {}
			
		}
		return result;
	}
	

	@Override
	public int modifyBookDB(J20_BookDBVO1 bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(updateSQL);
			
				
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
				ps.setString(4, bvo.getId());
		int cnt = ps.executeUpdate();
		if( cnt > 0 ) result = 1;
		}catch (Exception ex ) {
			ex.printStackTrace();
		}finally{
			if( ps != null) 
				try { ps.close(); } catch(Exception e){}
			if( conn != null)
				try{ conn.close(); } catch ( Exception e ) {}
		}
		
		return result;
	}
	@Override
	public int removeBookDB(String Key) {
		Connection conn = null;
		PreparedStatement ps = null;
		int result = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, Key);
				int cnt = ps.executeUpdate();
				if(cnt>0) result = 1;
			
		} catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if( ps != null) 
				try { ps.close(); } catch(Exception e){}
			if( conn != null)
				try{ conn.close(); } catch ( Exception e ) {}
		}
		
		return result;
	}
}
